package com.foreknow.demo14.demo14Z;

import com.foreknow.demo14.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBUtilZy {
    private Connection conn;
    private Statement statement;
    private ResultSet rs;
    /**
     * 连接数据库
     */
    public Connection getConnection() throws ClassNotFoundException, SQLException {
        //1. 加载驱动程序
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2. 获取连接Connection
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/base23?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC","root","123456");
        return conn;
    }


    /**
     * 查询
     */
    public List<User> QueryInfo(String sql) throws SQLException {
        statement = conn.createStatement();
        rs = statement.executeQuery(sql);
        List<User> list = new ArrayList<>();
        while (rs.next()) {
            //获取到当前行数据(第一列，第二列.....第n列)
            int id = rs.getInt("id"); //获取到第一列的值
            String username = rs.getString("username");//获取到第二列的值
            String password = rs.getString("password");//获取到第三列的值
            //System.out.println(id+"---"+username+"---"+password);
            User user = new User();
            user.setId(id);
            user.setUsername(username);
            user.setPassword(password);
            //将对象保存到集合中
            list.add(user);
        }
        return list;
    }


    /**
     * DML(数据操作)
     * insert delete update
     */
    public boolean dml(String sql) throws SQLException {
        statement = conn.createStatement();
        int isRight = statement.executeUpdate(sql);
        return isRight == 1;
    }

    /**
     * 关闭连接
     */
    public void closeAll() throws SQLException {
        if(rs!=null) {
            rs.close();
        }
        if(statement!=null) {
            statement.close();
        }
        if(conn!=null) {
            conn.close();
        }
    }

    public static void main(String[] args) {
        DBUtilZy dbUtil = new DBUtilZy();
        try {
            //连接数据库
            dbUtil.getConnection();
//            List<User> list = dbUtil.QueryInfo("select * from user");
//            for (User user:list) {
//                System.out.println(user.getUsername());
//            }
            boolean isRigth = dbUtil.dml("INSERT INTO user(username,password) VALUES('xy123','111111')");
            if(isRigth) {
                System.out.println("添加成功......");
            }else {
                System.out.println("添加失败......");
            }
            dbUtil.closeAll();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
